<?php

namespace App\Http\Controllers\TaskSalary;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class LineAverTaskSalaryController extends Controller
{
    /**
     * 单车线路均值汇总表
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function lineTaskDalaryList(Request $request)
    {
        $params = $request->all();
        $data = $this->getLineTaskDalaryList($params);
        if ($data) {
            $dateRange = $params['dateRange'];
            $start_date_time = date('Y-m-d', strtotime($dateRange[0]));
            $end_date_time = date('Y-m-d', strtotime($dateRange[1]));
            $result = $this->transData($data, $start_date_time, $end_date_time, $params['sort'], $params['sort_order']);
        }

        return response()->formatJson(['result' => $result]);
    }

    public function getLineTaskDalaryList($params)
    {
        //不考核车辆
        $noCheckBus = DB::table('akbus_bus')->where('is_check', 1)->pluck('license_plate_num')->toArray();
        $income_query = DB::table('v_income_pay')
            ->select(
                '车牌号',
                '线路',
                DB::raw('SUM(包车收入) as total_chartered_bus'),
                DB::raw('SUM(一卡通收入) as total_IPass'),
                DB::raw('SUM(邮储收入) as total_post'),
                DB::raw('SUM(现金收入) as total_cash'),
                DB::raw('SUM(包车收入)+SUM(一卡通收入)+SUM(邮储收入)+SUM(现金收入) as total_income')
            );
        //顺康
        $sk_query = DB::table('v_gas_sk')->select(
            '车牌号',
            '线路',
            DB::raw('SUM(金额) as total_sk_money'),
            DB::raw('SUM(气量) as total_sk_air')
        );
        // ->whereIn('车牌号',$noCheckBus)
        //逸华
        $yh_query = DB::table('v_gas_yh')->select(
            '车牌号',
            '线路',
            DB::raw('SUM(金额) as total_yh_money'),
            DB::raw('SUM(气量) as total_yh_air')
        );
        //月总公里数(一条线路停运，6辆车)
        $mileage_query = DB::table('akbus_mileage')->select(
            'license_plate_num',
            DB::raw('SUM(gps_mileage) as total_km'),
            'plan_times',
            'actual_times',
            DB::raw('SUM(drop_off) as drop_off')
        );
        $dateRange = $params['dateRange'];
        $start_date_time = date('Y-m-d', strtotime($dateRange[0]));
        $end_date_time = date('Y-m-d', strtotime($dateRange[1]));

        //每条线路   车辆数
        // $lineBus_query = DB::table('akbus_bus_distribution')
        //     ->select('line', 'license_plate_num')
        //     ->where($start_date_time '>=', DB::raw('to_days(date)'))
        //     ->where($end_date_time, '<=', DB::raw('to_days(end_date)'));

        $income_query->whereBetween('日期', [$start_date_time, $end_date_time]);
        $sk_query->whereBetween(
            '日期',
            [date('Y-m-d 00:00:00', strtotime($start_date_time)), date('Y-m-d 23:59:59', strtotime($end_date_time))]
        );
        $yh_query->whereBetween('日期', [$start_date_time, $end_date_time]);
        $mileage_query->whereBetween('date', [$start_date_time, $end_date_time]);

        $income_query->where('线路', '=', $params['line']);
        $sk_query->where('线路', '=', $params['line']);
        $yh_query->where('线路', '=', $params['line']);
        $mileage_query->where('line', '=', $params['line']);
        // $lineBus_query->where('line', '=', $params['line']);
        // $data['lineBusData'] = $lineBusData;
        $incomeData = $income_query->groupBy('车牌号')->orderByDesc('total_income')->get()->toArray();
        $skData = $sk_query->groupBy('车牌号')->get()->toArray();
        $yhData = $yh_query->groupBy('车牌号')->get()->toArray();//->whereIn('类型', [1, 2])
        $mileageData = $mileage_query->groupBy('license_plate_num')->get()->toArray();
        // $lineBusData = $lineBus_query->get()->toArray();
        $data['incomeData'] = $incomeData;
        $data['skData'] = $skData;
        $data['yhData'] = $yhData;
        $data['mileageData'] = $mileageData;
        // $data['lineBusData'] = $lineBusData;
        return $data;
    }

    public function transData($data, $start_date_time, $end_date_time, $sort, $sort_order)
    {
        $dataInfo = [];
        foreach ($data['incomeData'] as $key => $v) {
            $dataInfo[$key]['license_plate_num'] = $v['车牌号'];
            $dataInfo[$key]['line'] = $v['线路'];
            $dataInfo[$key]['remark'] = '';

            if (count($data['skData']) > 0) {
                $skCollect = collect($data['skData'])->where('车牌号', $v['车牌号'])->first();
                $dataInfo[$key]['total_sk_money'] = $skCollect['total_sk_money'];
                $dataInfo[$key]['total_sk_air'] = $skCollect['total_sk_air'];
            } else {
                $dataInfo[$key]['total_sk_money'] = 0;
                $dataInfo[$key]['total_sk_air'] = 0;
            }

            if (count($data['yhData']) > 0) {
                $yhCollect = collect($data['yhData'])->where('车牌号', $v['车牌号'])->first();
                $dataInfo[$key]['total_yh_money'] = $yhCollect['total_yh_money'];
                $dataInfo[$key]['total_yh_air'] = $yhCollect['total_yh_air'];
            } else {
                $dataInfo[$key]['total_yh_money'] = 0;
                $dataInfo[$key]['total_yh_air'] = 0;
            }

            if (count($data['mileageData']) > 0) {
                $mileageCollect = collect($data['mileageData'])
                    ->where('license_plate_num', $v['车牌号'])->first();
                $dataInfo[$key]['total_km'] = $mileageCollect['total_km'];
                //掉躺
                $drop_off = DB::table('akbus_mileage_drop')
                            ->where('bus_num',$v['车牌号'])
                            ->whereBetween('date',[$start_date_time,$end_date_time])
                            ->sum('drop_trip');
                $dataInfo[$key]['drop_off'] = $drop_off;
            } else {
                $dataInfo[$key]['total_km'] = 0;
                $dataInfo[$key]['drop_off'] = 0;
            }

            // $rank = 1;
            // foreach ($data['incomeData'] as $i_v) {
            //     if ($v['license_plate_num'] === $i_v['车牌号']) {
            // $dataInfo[$key]['ranking'] = $rank;
            $dataInfo[$key]['total_income'] = $v['total_chartered_bus'] + $v['total_IPass'] +
                $v['total_post'] + $v['total_cash'];
            //     }
            //     // $rank++;
            // }
        }
        $days = $this->diffBetweenTwoDays($start_date_time, $end_date_time);
        $result = [];
        foreach ($dataInfo as $d_k => $d_v) {
            $result[$d_k] = $d_v;
            $result[$d_k]['total_sk_money'] = isset($d_v['total_sk_money']) ?
                round((float) $result[$d_k]['total_sk_money'], 2) :
                $result[$d_k]['total_sk_air'] = isset($d_v['total_sk_air']) ?
                    round((float) $result[$d_k]['total_sk_air'], 2) : 0;
            $result[$d_k]['total_yh_money'] = isset($d_v['total_yh_money']) ?
                round((float) $result[$d_k]['total_yh_money'], 2) : 0;
            $result[$d_k]['total_yh_air'] = isset($d_v['total_yh_air']) ?
                round((float) $result[$d_k]['total_yh_air'], 2) : 0;
            $result[$d_k]['total_km'] = isset($d_v['total_km']) ?
                round((float) $result[$d_k]['total_km'], 2) : 0;
            $result[$d_k]['total_income'] = isset($d_v['total_income']) ?
                round((float) $result[$d_k]['total_income'], 2) : 0;
            $result[$d_k]['drop_off'] = isset($d_v['drop_off']) ?
                (float) $result[$d_k]['drop_off'] : 0;
            $result[$d_k]['total_sk_yh_air'] = round((float) $result[$d_k]['total_sk_air'], 2) +
                round((float) $result[$d_k]['total_yh_air'], 2);
            $result[$d_k]['total_sk_yh_money'] = round((float) $result[$d_k]['total_sk_money'], 2) +
                round((float) $result[$d_k]['total_yh_money'], 2);
            if ($days > 0) {
                $result[$d_k]['aver_sk_yh_money'] = $result[$d_k]['total_sk_yh_money'] / $days; //气耗/天
            } else {
                $result[$d_k]['aver_sk_yh_money'] = $result[$d_k]['total_sk_yh_money'];
            }
            $result[$d_k]['total_sk_yh_money'] = round($result[$d_k]['total_sk_yh_money'], 2);
            $result[$d_k]['aver_sk_yh_money'] = round($result[$d_k]['aver_sk_yh_money'], 2);
            if ($result[$d_k]['total_km'] == 0) {
                $result[$d_k]['aver_money_km'] = 0; //元/百公里
                $result[$d_k]['aver_air_km'] = 0; //m³/百公里
                $result[$d_k]['aver_income_km'] = 0; //收入/百公里
            } else {
                $result[$d_k]['aver_money_km'] =
                    round($result[$d_k]['total_sk_yh_money'] / $result[$d_k]['total_km'] * 100, 2); //元/百公里
                $result[$d_k]['aver_air_km'] =
                    round($result[$d_k]['total_sk_yh_air'] / $result[$d_k]['total_km'] * 100, 2); //m³/百公里
                $result[$d_k]['aver_income_km'] =
                    round($result[$d_k]['total_income'] / $result[$d_k]['total_km'] * 100, 2); //收入/百公里
            }
            $divisor = (8 * $days - $result[$d_k]['drop_off']);
            if ($divisor == 0) {
                $result[$d_k]['aver_money_off'] = 0; //气耗/趟
                $result[$d_k]['aver_return_km'] = 0; //往返里程
                $result[$d_k]['aver_income_off'] = 0; //收入/趟
            } else {
                $result[$d_k]['aver_money_off'] = $result[$d_k]['total_sk_yh_money'] / $divisor; //气耗/趟
                $result[$d_k]['aver_return_km'] = $result[$d_k]['total_km'] / $divisor; //往返里程
                $result[$d_k]['aver_income_off'] = $result[$d_k]['total_income'] / $divisor; //收入/趟
            }
            $result[$d_k]['aver_money_off'] = round($result[$d_k]['aver_money_off'], 2);
            $result[$d_k]['aver_return_km'] = round($result[$d_k]['aver_return_km'], 2);
            $result[$d_k]['aver_income_off'] = round($result[$d_k]['aver_income_off'], 2);
        }

        $lineSort = collect($result)->sortByDesc('aver_air_km')->toArray();
        $i = 1;
        foreach ($lineSort as $line_k => $line_v) {
            $lineSort[$line_k]['ranking'] = $i;
            $i++;
        }
        if ($sort_order == 'desc') {
            $lineSortResult = collect($lineSort)->sortByDesc($sort)->toArray();
        } elseif ($sort_order == "asc") {
            $lineSortResult = collect($lineSort)->sortBy($sort)->toArray();
        } else {
            $lineSortResult = $lineSort;
        }
        $lines = collect($lineSortResult)->groupBy('line')->toArray();
        $dataRes = [];
        foreach ($lines as $line) {
            $total_sk_yh_money_count = 0;
            $total_sk_money_count = 0;
            $total_yh_money_count = 0;
            $total_km_count = 0;
            $total_income_count = 0;
            $aver_sk_yh_money_count = 0;
            $aver_money_km_count = 0;
            $aver_air_km_count = 0;
            $aver_income_km_count = 0;
            $total_sk_air_count = 0;
            $total_yh_air_count = 0;
            $aver_money_off_count = 0;
            $aver_return_km_count = 0;
            $aver_income_off_count = 0;
            $drop_off_count = 0;
            foreach ($line as $vvv) {
                $dataRes[] = $vvv;
                $total_sk_yh_money_count += (float) $vvv['total_sk_yh_money'];
                $total_sk_money_count += (float) $vvv['total_sk_money'];
                $total_yh_money_count += (float) $vvv['total_yh_money'];
                $total_km_count += (float) $vvv['total_km'];
                $total_income_count += (float) $vvv['total_income'];
                $aver_sk_yh_money_count += (float) $vvv['aver_sk_yh_money'];
                $aver_money_km_count += (float) $vvv['aver_money_km'];
                $aver_air_km_count += (float) $vvv['aver_air_km'];
                $aver_income_km_count += (float) $vvv['aver_income_km'];
                $total_sk_air_count += (float) $vvv['total_sk_air'];
                $total_yh_air_count += (float) $vvv['total_yh_air'];
                $aver_money_off_count += (float) $vvv['aver_money_off'];
                $aver_return_km_count += (float) $vvv['aver_return_km'];
                $aver_income_off_count += (float) $vvv['aver_income_off'];
                $drop_off_count += (float) $vvv['drop_off'];
            }
            $dataRes[] = [
                'license_plate_num' => '-',
                'line'              => '线路合计',
                'remark'            => '',
                'total_sk_money'    => round($total_sk_money_count, 2),
                'total_sk_air'      => round($total_sk_air_count, 2),
                'total_km'          => round($total_km_count, 2),
                'drop_off'          => round($drop_off_count, 2),
                'ranking'           => '-',
                'total_income'      => round($total_income_count, 2),
                'total_yh_money'    => round($total_yh_money_count, 2),
                'total_yh_air'      => round($total_yh_air_count, 2),
                'total_sk_yh_money' => round($total_sk_yh_money_count, 2),
                'aver_sk_yh_money'  => round($aver_sk_yh_money_count, 2),
                'aver_money_km'     => round($aver_money_km_count, 2),
                'aver_air_km'       => round($aver_air_km_count, 2),
                'aver_income_km'    => round($aver_income_km_count, 2),
                'aver_money_off'    => round($aver_money_off_count, 2),
                'aver_return_km'    => round($aver_return_km_count, 2),
                'aver_income_off'   => round($aver_income_off_count, 2),
            ];
            $dataRes[] = [
                'license_plate_num' => '-',
                'line'              => '线路平均',
                'remark'            => '',
                'total_sk_money'    => round($total_sk_money_count / count($line), 2),
                'total_sk_air'      => round($total_sk_air_count / count($line), 2),
                'total_km'          => round($total_km_count / count($line), 2),
                'drop_off'          => round($drop_off_count / count($line), 2),
                'ranking'           => '-',
                'total_income'      => round($total_income_count / count($line), 2),
                'total_yh_money'    => round($total_yh_money_count / count($line), 2),
                'total_yh_air'      => round($total_yh_air_count, 2),
                'total_sk_yh_money' => round($total_sk_yh_money_count / count($line), 2),
                'aver_sk_yh_money'  => round($aver_sk_yh_money_count / count($line), 2),
                'aver_money_km'     => round($aver_money_km_count / count($line), 2),
                'aver_air_km'       => round($aver_air_km_count / count($line), 2),
                'aver_income_km'    => round($aver_income_km_count / count($line), 2),
                'aver_money_off'    => round($aver_money_off_count / count($line), 2),
                'aver_return_km'    => round($aver_return_km_count / count($line), 2),
                'aver_income_off'   => round($aver_income_off_count / count($line), 2),
            ];
        }
        return $dataRes;
    }

    public function diffBetweenTwoDays($start_date_time, $end_date_time)
    {
        $start = strtotime($start_date_time);
        $end = strtotime($end_date_time);
        return ($end - $start) / 86400;
    }

    /**
     * 导出单车线路均值汇总表
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportLineTaskDalary(Request $request)
    {
        $params = $request->input();
        $params['dateRange'] = [$params['start_date_time'], $params['end_date_time']];
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $result = $this->getLineTaskDalaryList($params);
        if ($result) {
            $data = $this->transData($result, $params['start_date_time'], $params['end_date_time'], '', '');
        } else {
            $data = [];
        }

        // 删除无用行
        array_pop($data);
        array_pop($data);

        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $start = date('Y/m/d', strtotime($params['start_date_time']));
        $end = date('Y/m/d', strtotime($params['end_date_time']));
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', $start.' - '.$end.' 单 车 线 路 均 值 汇 总 表')
            ->setCellValue('A2', $params['line'].'路')
            ->setCellValue('B2', '气耗')->setCellValue('C2', '顺康')->setCellValue('D2', '逸华')
            ->setCellValue('E2', '公里')->setCellValue('F2', '收入')->setCellValue('G2', '气耗/天')
            ->setCellValue('H2', '元/百公里')->setCellValue('I2', 'm³/百公里')->setCellValue('J2', '气量(顺康）')
            ->setCellValue('K2', '气量(逸华）')->setCellValue('L2', '收入/百公里')->setCellValue('M2', '排名')
            ->setCellValue('N2', '气耗/趟')->setCellValue('O2', '往返里程')->setCellValue('P2', '收入/趟')
            ->setCellValue('Q2', '掉趟')->setCellValue('R2', '备注');

        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(29);
        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(29);
        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        for ($i = 0; $i < 18; $i++) {
            $spreadsheet->getActiveSheet()->getColumnDimension($Letter[$i])->setWidth(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $num = 3;
        $total_sk_yh_money_count = 0;
        $total_sk_money_count = 0;
        $total_yh_money_count = 0;
        $total_km_count = 0;
        $total_income_count = 0;
        $aver_sk_yh_money_count = 0;
        $aver_money_km_count = 0;
        $aver_air_km_count = 0;
        $aver_income_km_count = 0;
        $total_sk_air_count = 0;
        $total_yh_air_count = 0;
        $aver_money_off_count = 0;
        $aver_return_km_count = 0;
        $aver_income_off_count = 0;
        $drop_off_count = 0;
        foreach ($data as $dv) {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $dv['license_plate_num']);
            $spreadsheet->getActiveSheet()->setCellValue('B'.$num, $dv['total_sk_yh_money']);
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $dv['total_sk_money']);
            $spreadsheet->getActiveSheet()->setCellValue('D'.$num, $dv['total_yh_money']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $dv['total_km']);
            $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $dv['total_income']);
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $dv['aver_sk_yh_money']);
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $dv['aver_money_km']);
            $spreadsheet->getActiveSheet()->setCellValue('I'.$num, $dv['aver_air_km']);
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $dv['total_sk_air']);
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $dv['total_yh_air']);
            $spreadsheet->getActiveSheet()->setCellValue('L'.$num, $dv['aver_income_km']);
            $spreadsheet->getActiveSheet()->setCellValue('M'.$num, $dv['ranking']);
            $spreadsheet->getActiveSheet()->setCellValue('N'.$num, $dv['aver_money_off']);
            $spreadsheet->getActiveSheet()->setCellValue('O'.$num, $dv['aver_return_km']);
            $spreadsheet->getActiveSheet()->setCellValue('P'.$num, $dv['aver_income_off']);
            $spreadsheet->getActiveSheet()->setCellValue('Q'.$num, $dv['drop_off']);
            $spreadsheet->getActiveSheet()->setCellValue('R'.$num, $dv['remark']);

            $total_sk_yh_money_count += (float) $dv['total_sk_yh_money'];
            $total_sk_money_count += (float) $dv['total_sk_money'];
            $total_yh_money_count += (float) $dv['total_yh_money'];
            $total_km_count += (float) $dv['total_km'];
            $total_income_count += (float) $dv['total_income'];
            $aver_sk_yh_money_count += (float) $dv['aver_sk_yh_money'];
            $aver_money_km_count += (float) $dv['aver_money_km'];
            $aver_income_km_count += (float) $dv['aver_income_km'];
            $aver_air_km_count += (float) $dv['aver_air_km'];
            $total_sk_air_count += (float) $dv['total_sk_air'];
            $total_yh_air_count += (float) $dv['total_yh_air'];
            $aver_money_off_count += (float) $dv['aver_money_off'];
            $aver_return_km_count += (float) $dv['aver_return_km'];
            $aver_income_off_count += (float) $dv['aver_income_off'];
            $drop_off_count += (float) $dv['drop_off'];
            $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(29);
            for ($i = 0; $i < 19; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->getFont()->setSize(10);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].$num)->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $num++;
        }
        for ($i = 0; $i < 18; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + 1))->getFont()->setSize(10);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + 1))->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + 2))->getFont()->setSize(10);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + 2))->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->setCellValue('A'.($num + 1), '线路合计');
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + 1), $total_sk_yh_money_count);
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + 1), $total_sk_money_count);
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + 1), $total_yh_money_count);
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + 1), $total_km_count);
        $spreadsheet->getActiveSheet()->setCellValue('F'.($num + 1), $total_income_count);
        $spreadsheet->getActiveSheet()->setCellValue('G'.($num + 1), $aver_sk_yh_money_count);
        $spreadsheet->getActiveSheet()->setCellValue('H'.($num + 1), $aver_money_km_count);
        $spreadsheet->getActiveSheet()->setCellValue('I'.($num + 1), $aver_air_km_count);
        $spreadsheet->getActiveSheet()->setCellValue('J'.($num + 1), $total_sk_air_count);
        $spreadsheet->getActiveSheet()->setCellValue('K'.($num + 1), $total_yh_air_count);
        $spreadsheet->getActiveSheet()->setCellValue('L'.($num + 1), '-');
        $spreadsheet->getActiveSheet()->setCellValue('M'.($num + 1), '-');
        $spreadsheet->getActiveSheet()->setCellValue('N'.($num + 1), $aver_money_off_count);
        $spreadsheet->getActiveSheet()->setCellValue('O'.($num + 1), $aver_return_km_count);
        $spreadsheet->getActiveSheet()->setCellValue('P'.($num + 1), $aver_income_off_count);
        $spreadsheet->getActiveSheet()->setCellValue('Q'.($num + 1), $drop_off_count);
        $spreadsheet->getActiveSheet()->setCellValue('R'.($num + 1), '-');
        $spreadsheet->getActiveSheet()->setCellValue('A'.($num + 2), '线路均值');
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + 2), $total_sk_yh_money_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + 2), $total_sk_money_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + 2), $total_yh_money_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + 2), $total_km_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('F'.($num + 2), $total_income_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('G'.($num + 2), $aver_sk_yh_money_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('H'.($num + 2), $aver_money_km_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('I'.($num + 2), $aver_air_km_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('J'.($num + 2), $total_sk_air_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('K'.($num + 2), $total_yh_air_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('L'.($num + 2), $aver_income_km_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('M'.($num + 2), '-');
        $spreadsheet->getActiveSheet()->setCellValue('N'.($num + 2), $aver_money_off_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('O'.($num + 2), $aver_return_km_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('P'.($num + 2), $aver_income_off_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('Q'.($num + 2), $drop_off_count / ($num - 3));
        $spreadsheet->getActiveSheet()->setCellValue('R'.($num + 2), '-');

        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:R1');
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

        // 设置右对齐
        $spreadsheet->getActiveSheet()->getStyle('B3:L'.($num + 2))->applyFromArray($numberStyleCenter);
        $spreadsheet->getActiveSheet()->getStyle('N3:Q'.($num + 2))->applyFromArray($numberStyleCenter);
        // 设置千分位
        $spreadsheet->getActiveSheet()->getStyle('B3:L'.($num + 2))->getNumberFormat()->setFormatCode('#,##0.00');
        $spreadsheet->getActiveSheet()->getStyle('N3:P'.($num + 2))->getNumberFormat()->setFormatCode('#,##0.00');
        // 设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(13);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(13);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(13);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(13);
        $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight(29);
        $spreadsheet->getActiveSheet()->getRowDimension($num + 1)->setRowHeight(29);
        $spreadsheet->getActiveSheet()->getRowDimension($num + 2)->setRowHeight(29);
        // 设置边框
        $borderStyleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color'       => ['argb' => '000000000'],
                ],
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1:R'.($num + 2))->applyFromArray($borderStyleArray);
        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $spreadsheet->getActiveSheet()->getStyle('A2')->getFont()->setSize(18);
        $spreadsheet->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        $spreadsheet->getActiveSheet()->getStyle('A'.($num + 1))->getFont()->setSize(12);
        $spreadsheet->getActiveSheet()->getStyle('A'.($num + 1))->getFont()->setBold(true);
        $spreadsheet->getActiveSheet()->getStyle('A'.($num + 2))->getFont()->setSize(12);
        $spreadsheet->getActiveSheet()->getStyle('A'.($num + 2))->getFont()->setBold(true);
        $spreadsheet->setActiveSheetIndex(0);
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle($params['line'].'路');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="月单车汇总表.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}
